package com.zdkj.subway.check.controller.subwayRat.TVMRevenueVarianceAudit.TvmChaYiDXC.tvmCengJiCYXC.tvmXianJinCengJiCYDXC;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.zdkj.common.core.exceltools.ExcelUtil;
import com.zdkj.common.core.exceltools.ExcelVersion;
import com.zdkj.common.core.util.R;
import com.zdkj.common.data.cache.RedisTemplateConfig;
import com.zdkj.common.log.annotation.SysLog;
import com.zdkj.common.security.annotation.Inner;
import com.zdkj.common.security.service.PigxUser;
import com.zdkj.common.security.util.SecurityUtils;
import com.zdkj.subway.check.dto.SwAuInternetTicketEquipmentDto;
import com.zdkj.subway.check.entity.*;
import com.zdkj.subway.check.enums.ResultDetailMsg;
import com.zdkj.subway.check.mapper.*;
import com.zdkj.subway.check.result.Result;
import com.zdkj.subway.check.service.impl.subwayRat.TVMRevenueVarianceAudit.TvmChaYiDXC.tvmCengJiCYXC.tvmXianJinCengJiCYDXC.TvmXianJinCengJiCYDXCService;
import com.zdkj.subway.check.util.*;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.core.io.InputStreamSource;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 互联网售票机
 */
@RestController
@RequestMapping("/check")
@Slf4j
public class HulianShouPiaoJiController {

    @Resource
    private TvmXianJinCengJiCYDXCService tvmXianJinCengJiCYDXCService;

    @Resource
    private CashboxUninstallMapper cashboxUninstallMapper;
    @Resource
    private SwAuInternetTicketEquipmentMapper swAuInternetTicketEquipmentMapper;
    @Resource
    private SwAuInternetTicketEquipmentDetailsMapper swAuInternetTicketEquipmentDetailsMapper;
    @Resource
    SwPmDeviceOperationCodeMapper swPmDeviceOperationCodeMapper;
    @Resource
    RedisTemplateConfig redisTemplateConfig;
    @Resource
    SwParamStationMapper swParamStationMapper;
    @Resource
    SwParamLineMapper swParamLineMapper;
    /**
     * 互联网售票机数据录入
     * @param page
     * @param swAuInternetTicketEquipment
     * @return
     */
    @RequestMapping("/getSwAuInputInternetTicketEquipmentList")
    @ApiOperation(value = "互联网售票机数据录入", notes = "互联网售票机数据录入")
    public Page getSwAuInputInternetTicketEquipmentList(Page page, SwAuInternetTicketEquipment swAuInternetTicketEquipment){
        //查询列表
        List<Map<String,Object>> res=tvmXianJinCengJiCYDXCService.getSwAuInputInternetTicketEquipmentList(swAuInternetTicketEquipment);
        page.setData(res,true);
        return page;
    }

    /**
     * 修改互联网售票机数据（添加操作或者修改）
     * @param details
     * @return
     */
    @RequestMapping("/mondifySwAuInputInternetTicketEquipmentDetail")
    @ApiOperation(value = "修改互联网售票机数据（添加操作或者修改）", notes = "修改互联网售票机数据（添加操作或者修改）")
    public Result mondifySwAuInputInternetTicketEquipmentDetail(String details, SwAuInternetTicketEquipment swAuInternetTicketEquipment){
        Result result=new Result(ResultDetailMsg.OK);
        try {
            Integer res=tvmXianJinCengJiCYDXCService.mondifySwAuInputInternetTicketEquipmentDetail(details, swAuInternetTicketEquipment);
            return result;
        }catch (Exception e){
            log.error("{}",e);
            result.setResult(ResultDetailMsg.CHECK_UPDATE_ERROR);
        }
        return result;
    }

    /**
     * 修改互联网售票机数据
     * @param swAuInternetTicketEquipment
     * @return
     */
    @RequestMapping("/updateSwAuInputInternetTicketEquipment")
    @ApiOperation(value = "修改互联网售票机数据", notes = "修改互联网售票机数据")
    public Result updateSwAuInputInternetTicketEquipment(String details, SwAuInternetTicketEquipment swAuInternetTicketEquipment){
        Result result=new Result(ResultDetailMsg.OK);
        try {
            UpdateWrapper<SwAuInternetTicketEquipment> updateWrapper=new UpdateWrapper<>();
            updateWrapper.eq("id", swAuInternetTicketEquipment.getId());
            swAuInternetTicketEquipment.setUpdateTime(new Date());
            Integer res= swAuInternetTicketEquipmentMapper.update(swAuInternetTicketEquipment,updateWrapper);
            return result;
        }catch (Exception e){
            log.error("{}",e);
            result.setResult(ResultDetailMsg.CHECK_UPDATE_ERROR);
        }
        return result;
    }

    /**
     * 互联网售票机数据 替换确认金额为协查金额
     * @param ids
     * @return
     */
    @RequestMapping("/replaceSwAuInputInternetTicketEquipmentJine")
    @ApiOperation(value = "互联网售票机数据 替换确认金额为协查金额", notes = "互联网售票机数据 替换确认金额为协查金额")
    public Result replaceSwAuInputInternetTicketEquipmentJine(String ids,SwAuInternetTicketEquipment swAuInternetTicketEquipment){
        Result result=new Result(ResultDetailMsg.OK);
        try {
            swAuInternetTicketEquipment.setUpdateTime(new Date());
            UpdateWrapper<SwAuInternetTicketEquipment> updateWrapper=new UpdateWrapper<>();
            updateWrapper.setSql("confirm_amount=assist_amount");
            updateWrapper.in("id",ids.split(","));
            Integer res= swAuInternetTicketEquipmentMapper.update(swAuInternetTicketEquipment,updateWrapper);
            return result;
        }catch (Exception e){
            log.error("{}",e);
            result.setResult(ResultDetailMsg.CHECK_UPDATE_ERROR);
        }
        return result;
    }

    /**
     * 获取互联网售票机线路数据
     * @param ids
     * @param swAuInternetTicketEquipment
     * @return
     */
    @RequestMapping("/getLineCodeWithSwAuInputInternetTicketEquipment")
    @ApiOperation(value = "获取互联网售票机线路数据", notes = "获取互联网售票机线路数据")
    public Result getLineCodeWithSwAuInputInternetTicketEquipment(String ids,SwAuInternetTicketEquipment swAuInternetTicketEquipment){
        Result result=new Result(ResultDetailMsg.OK);
        try {
            QueryWrapper<SwAuInternetTicketEquipment> queryWrapper=new QueryWrapper<>();
            queryWrapper.groupBy("line_code");
            queryWrapper.select("line_code as lineCode");
            List<SwAuInternetTicketEquipment> swAuInternetTicketEquipments = swAuInternetTicketEquipmentMapper.selectList(queryWrapper);
            result.setData(swAuInternetTicketEquipments);
            return result;
        }catch (Exception e){
            log.error("{}",e);
            result.setResult(ResultDetailMsg.CHECK_UPDATE_ERROR);
        }
        return result;
    }
    /**
     * 互联网售票机导出  分线路
     * @param lineCodes
     * @param swAuInternetTicketEquipment
     * @return
     */
    @ApiOperation(value = "互联网售票机导出,(日期) 必选,lineCodes(线路) 必选", notes = "互联网售票机导出,(日期) 必选,lineCodes(线路) 必选")
    @GetMapping("/exportInternetTicketEquipmentLine")
    public void exportInternetTicketEquipmentLine(String lineCodes,SwAuInternetTicketEquipment swAuInternetTicketEquipment,HttpServletResponse response) throws ParseException {
        String startDate = swAuInternetTicketEquipment.getStartTime();
        String endDate = swAuInternetTicketEquipment.getEndTime();
        PigxUser user = SecurityUtils.getUser();
        boolean isDateCondition = StringUtils.isNotEmpty(startDate) && StringUtils.isNotEmpty(endDate) && StringUtils.isNotEmpty(lineCodes);
        if(!isDateCondition){
            return;
        }
        QueryWrapper<SwAuInternetTicketEquipment> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("LINE_CODE, STATION_CODE, REVENUE_DATE, ifnull(sum(COMPARE_TOTAL_NUMBER_SALES),0) compareTotalNumberSales, ifnull(sum(COMPARE_SUBTOTAL_TOTAL),0) compareSubtotalTotal");
        queryWrapper.between("REVENUE_DATE",startDate,endDate);
        queryWrapper.in("LINE_CODE",lineCodes.split(","));
        queryWrapper.groupBy("LINE_CODE","STATION_CODE","REVENUE_DATE");
        queryWrapper.orderByAsc("LINE_CODE","STATION_CODE","REVENUE_DATE");
        //互联网售票机数据
        List<SwAuInternetTicketEquipment> list = swAuInternetTicketEquipmentMapper.selectList(queryWrapper);

        Map<Integer, List<SwAuInternetTicketEquipmentDto>> map = new HashMap<>();
        Map<Integer, LocalDate> mapLocalDate = new HashMap<>();
        Map<Integer, Integer> mapSumNumber = new HashMap<>();
        Map<Integer, BigDecimal> mapSumTotal = new HashMap<>();
        List<Map<String,String>> stationMap = swAuInternetTicketEquipmentMapper.selectStationMap();
        if(CollectionUtils.isNotEmpty(list)){
            List<String> betweenTwoDate = DateFormat.getDatesBetweenTwoDate(startDate, endDate);
            for (int i = 0; i < betweenTwoDate.size(); i++) {
                String  currentDate = betweenTwoDate.get(i);
                LocalDate localCurrentDate = LocalDate.parse(currentDate, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                //当前日期后两位
                int iDate = Integer.valueOf(currentDate.substring(currentDate.length() - 2));

                List<SwAuInternetTicketEquipment> collect = list.stream().filter(x -> currentDate.equals(x.getRevenueDate())).collect(Collectors.toList());
                List<SwAuInternetTicketEquipmentDto> records  = new ArrayList<>();

                BigDecimal hundred = new BigDecimal("100");
                for(SwAuInternetTicketEquipment obj : collect) {
                    SwAuInternetTicketEquipmentDto dto = new SwAuInternetTicketEquipmentDto();
                    //线路
                    String lineName = getExportLineName(stationMap,obj.getLineCode());
                    dto.setLineName(lineName);
                    //站点
                    String stationName = getExportStationName(stationMap,obj.getStationCode(),obj.getLineCode());
                    dto.setStationName(stationName);
                    //发售数量/张对比
                    dto.setCompareTotalNumberSales(obj.getCompareTotalNumberSales());
                    //小计金额/元对比
                    dto.setCompareSubtotalTotal(new BigDecimal(String.valueOf(obj.getCompareSubtotalTotal())).divide(hundred,2,BigDecimal.ROUND_HALF_UP));

                    records.add(dto);
                }
                //合计张数
                int sumNumber = collect.stream().mapToInt(SwAuInternetTicketEquipment::getCompareTotalNumberSales).sum();
                //合计金额
                BigDecimal sumTotal = collect.stream().map(SwAuInternetTicketEquipment::getCompareSubtotalTotal).reduce(BigDecimal.ZERO, BigDecimal::add).divide(hundred,2,BigDecimal.ROUND_HALF_UP);
                map.put(iDate,records);
                mapLocalDate.put(iDate,localCurrentDate);
                mapSumNumber.put(iDate,sumNumber);
                mapSumTotal.put(iDate,sumTotal);
            }
            InputStream excelTemplateIs = null;
            try {
                response.reset(); // 非常重要
                response.setContentType("application/vnd.ms-excel");
                response.setCharacterEncoding("utf-8");
                final String fileName = URLEncoder.encode("西安地铁互联网售票机售卖对账确认表", "UTF-8");
                response.setHeader("Content-disposition", "attachment;filename=" + fileName + System.currentTimeMillis() + ".xlsx");

                // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
                // {} 代表普通变量 {.} 代表是list的变量

                excelTemplateIs = this.getClass().getResourceAsStream("/static/excel/export/SwAuInternetTicketEquipmentLinesExcelTemplate.xlsx");

                ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).registerConverter(new LocalDateTimeConverter()).registerConverter(new LocalDateConverter()).withTemplate(excelTemplateIs).build();

                for (Map.Entry<Integer, List<SwAuInternetTicketEquipmentDto>> integerListEntry : map.entrySet()) {
                    Integer key = integerListEntry.getKey();
                    List<SwAuInternetTicketEquipmentDto> value = integerListEntry.getValue();
                    // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
                    // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
                    // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
                    // 如果数据量大 list不是最后一行 参照另一个
                    WriteSheet writeSheet = EasyExcel.writerSheet(key-1,String.valueOf(key)).build();
                    FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                    excelWriter.fill(value, fillConfig, writeSheet);
                    Map<String, Object> mapDate = new HashMap<>();
                    mapDate.put("currDate", mapLocalDate.get(key));
                    mapDate.put("createName", user.getNikeName());
                    mapDate.put("sumNumber", mapSumNumber.get(key));
                    mapDate.put("sumTotal", mapSumTotal.get(key));
                    excelWriter.fill(mapDate,writeSheet);
                }
                excelWriter.finish();
            } catch (Exception e) {
                e.printStackTrace();
                log.error("导出西安地铁互联网售票机售卖对账确认表错误");
            } finally {
                if(excelTemplateIs != null){
                    try {
                        excelTemplateIs.close();
                    } catch (IOException ioe) {
                        ioe.printStackTrace();
                    }

                }
            }
        }

    }

    /**
     * 线路名称转换
     * @param stationMap
     * @param lineCode
     * @return
     */
    private String getExportLineName (List<Map<String,String>> stationMap,String lineCode) {

        for (Map<String,String> map : stationMap) {
            if(lineCode.equals(map.get("LINE_CODE"))) {
                return map.get("LINE_NAME");
            }
        }
        return "";
    }

    /**
     * 站点名称转换
     * @param stationMap
     * @param stationCode
     * @param lineCode
     * @return
     */
    private String getExportStationName (List<Map<String,String>> stationMap,String stationCode,String lineCode) {

        for (Map<String,String> map : stationMap) {
            if(stationCode.equals(map.get("STATION_CODE")) && lineCode.equals(map.get("LINE_CODE"))) {
                return map.get("STATION_NAME");
            }
        }
        return "";
    }
    /**
     * 互联网售票机导出  分日期
     * @param lineCodes
     * @param swAuInternetTicketEquipment
     * @return
     */
    @RequestMapping("/exportInternetTicketEquipmentDate")
    @ApiOperation(value = "互联网售票机导出  分日期", notes = "互联网售票机导出  分日期")
    public Result exportInternetTicketEquipmentDate(String lineCodes,SwAuInternetTicketEquipment swAuInternetTicketEquipment){
        Result result=new Result(ResultDetailMsg.OK);
        try {
            QueryWrapper<SwAuInternetTicketEquipment> queryWrapper=new QueryWrapper<>();
//            queryWrapper.setEntity(swAuInternetTicketEquipment);
//            queryWrapper.ge("start_time", swAuInternetTicketEquipment.getStartTime());
//            queryWrapper.le("end_time", swAuInternetTicketEquipment.getEndTime());
            queryWrapper.between("REVENUE_DATE",swAuInternetTicketEquipment.getStartTime(),swAuInternetTicketEquipment.getEndTime());
            if(null != lineCodes){
                queryWrapper.in("line_code",lineCodes.split(","));
            }
            List<SwAuInternetTicketEquipment> swAuInternetTicketEquipments = swAuInternetTicketEquipmentMapper.selectList(queryWrapper);
            result.setData(swAuInternetTicketEquipments);
            return result;
        }catch (Exception e){
            log.error("{}",e);
            result.setResult(ResultDetailMsg.CHECK_UPDATE_ERROR);
        }
        return result;
    }

    /**
     * 互联网售票机数据详情
     * @param page
     * @param param
     * @return
     */
    @RequestMapping("/getSwAuInputInternetTicketEquipmentDetails")
    @ApiOperation(value = "互联网售票机数据详情", notes = "互联网售票机数据详情")
    public Page getSwAuInputInternetTicketEquipmentDetails(Page page, @RequestParam Map<String, Object> param){
        List<Map<String,Object>> res=tvmXianJinCengJiCYDXCService.getSwAuInputInternetTicketEquipmentDetails(param);
        page.setData(res,true);
        return page;
    }

    /**
     * 互联网售票机车站数据详情
     * @param
     * @param param
     * @return
     */
    @RequestMapping("/getSwStInputInternetTicketEquipmentDetails")
    @ApiOperation(value = "互联网售票机车站数据详情", notes = "互联网售票机车站数据详情")
    public Result getSwStInputInternetTicketEquipmentDetails( @RequestParam Map<String, Object> param) throws SQLException {
        Result rs=new Result(ResultDetailMsg.OK);
        String sql="SELECT sed.ID ,sed.equipment_code equipmentCode," +
                "sed.unit_price*100 unitPrice," +
                "sed.SALE_NUMBER saleNumber," +
                "sed.TOTAL_SALE_AMOUNT*100 totalSaleAmount" +
                " from sw_st_internet_ticket_equipment_details sed left join sw_st_internet_ticket_equipment se " +
                "on sed.RELATION_ID=se.id\n" +
                "where sed.DEL_FLAG=0 and se.LINE_CODE='"+param.get("lineCode")+"' " +
                " and se.STATION_CODE='"+param.get("stationCode")+"' " +
                " and se.START_TIME='"+param.get("startTime")+"' " +
                " and se.END_TIME='"+param.get("endTime")+"'";
        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
        rs.setData(maps);
        return rs;
    }

    /**
     * 互联网售票机数据录入审批
     * @param ids
     * @param chaYiIds
     * @return
     */
    @RequestMapping("/approvePassSwAuInputInternetTicketEquipment")
    @ApiOperation(value = "互联网售票机数据录入审批", notes = "互联网售票机数据录入审批")
    public Result approvePassSwAuInputInternetTicketEquipment(String ids,String chaYiIds){
        Result res=new Result();
        try {
            Integer up=tvmXianJinCengJiCYDXCService.approvePassSwAuInputInternetTicketEquipment(ids,chaYiIds);
        }catch (Exception e){
            log.error("{}",e);
            res.setResult(ResultDetailMsg.CHECK_UPDATE_ERROR);
            res.appendMsg("审批失败");
        }
        return res;
    }

    /**
     * 审批
     * @param ids
     * @param s
     * @return
     */
    @RequestMapping("/updateSwAuInputInternetTicketEquipmentDetails")
    @ApiOperation(value = "审批", notes = "审批")
    public Result updateSwAuInputInternetTicketEquipmentDetails(String ids,SwAuInternetTicketEquipmentDetails s){
        Result res=new Result();
        try {
            s.setUpdateTime(new Date());
            UpdateWrapper<SwAuInternetTicketEquipmentDetails> updateWrapper=new UpdateWrapper<>();
            updateWrapper.set("del_flag",1);
            updateWrapper.in("id",ids.split(","));
            Integer up= swAuInternetTicketEquipmentDetailsMapper.update(s,updateWrapper);
        }catch (Exception e){
            log.error("{}",e);
            res.setResult(ResultDetailMsg.CHECK_UPDATE_ERROR);
            res.appendMsg("审批失败");
        }
        return res;
    }

    /**
     * 互联网售票机数据录入提交
     * @param ids
     * @param s
     * @return
     */
    @RequestMapping("/submitSwAuInputInternetTicketEquipmentDetails")
    @ApiOperation(value = "互联网售票机数据录入提交", notes = "互联网售票机数据录入提交")
    public Result submitSwAuInputInternetTicketEquipmentDetails(String ids,SwAuInternetTicketEquipmentDetails s){
        Result res=new Result();
        try {

            Map<String,Object> param=new HashMap<>();
            param.put("approval_status", 0);
            List<SwAuInternetTicketEquipment> swAuInternetTicketEquipments = swAuInternetTicketEquipmentMapper.selectByMap(param);
            for(SwAuInternetTicketEquipment swAuInternetTicketEquipment : swAuInternetTicketEquipments){
                SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");
                Date startTime = dff.parse(swAuInternetTicketEquipment.getStartTime());
                Date endTime = dff.parse(swAuInternetTicketEquipment.getEndTime());
                Map<String,Object> param2=new HashMap<>();
                param2.put("startTime",startTime);
                param2.put("endTime",endTime);
                param2.put("lineCode", swAuInternetTicketEquipment.getLineCode());
                param2.put("stationCode", swAuInternetTicketEquipment.getStationCode());
                List<Map<String, Object>> r= swAuInternetTicketEquipmentDetailsMapper.jusuanChose(param2);
                if(r==null || r.size()==0){
                    continue;
                }
                Map<String, Object> stringObjectMap = r.get(0);
                BigDecimal compareTotalNumberSales = (BigDecimal)stringObjectMap.get("compareTotalNumberSales");
                BigDecimal compareSubtotalTotal =  (BigDecimal)stringObjectMap.get("compareSubtotalTotal");
                swAuInternetTicketEquipment.setCompareTotalNumberSales(compareTotalNumberSales.intValue());
                swAuInternetTicketEquipment.setCompareSubtotalTotal(compareSubtotalTotal);
                swAuInternetTicketEquipment.setUpdateTime(new Date());
//                swAuInternetTicketEquipment.setApprovalStatus(1); 是否置为已提交
                swAuInternetTicketEquipmentMapper.updateById(swAuInternetTicketEquipment);
            }
        }catch (Exception e){
            log.error("{}",e);
            res.setResult(ResultDetailMsg.CHECK_UPDATE_ERROR);
            res.appendMsg("提交审批失败");
        }
        return res;
    }

    /***
     * 线网导入互联网售票机明细
     * @param file
     * @param request
     * @return
     * @throws IOException
     */
    @RequestMapping("/uploadFileWithXianwang")
    @ApiOperation(value = "线网导入互联网售票机明细", notes = "线网导入互联网售票机明细")
    public Result uploadFileWithXianwang(@RequestParam MultipartFile file, HttpServletRequest request) throws IOException {
        Result result=new Result(ResultDetailMsg.OK);
        try {
            String fileName = file.getOriginalFilename();
            InputStream inputStream = file.getInputStream();
            Workbook wb=null;
            //根据文件后缀（xls/xlsx）进行判断
            if (fileName.endsWith("xls")){
//                FileInputStream fis = new FileInputStream(f);   //文件流对象
                wb = new HSSFWorkbook(inputStream);
            }else if (fileName.endsWith("xlsx")){
                wb = new XSSFWorkbook(inputStream);
            }else {
                result.setMsg("请使用xls、xlsx格式文件");
                result.setResult(ResultDetailMsg.CHECK_UPDATE_ERROR);
                return result;
            }
            List<Map<String,Object>> list=new ArrayList<>();
            Sheet sheetAt = wb.getSheetAt(0);
            String static_date="";
            for(int i=0;i<sheetAt.getLastRowNum();i++){
                Map<String,Object> one=new HashMap<>();
                Row row = sheetAt.getRow(i);
                if(i<=2){
                    if(i==0){
						String title  =  ExcelCellUtil.getCellContent(row.getCell(0));
                        static_date = title.substring(title.length()-10);
                    }
                    continue;
                }
                String lineName = row.getCell(0).getStringCellValue();
				if(lineName!=null && "合计".equals(lineName)){ //遇到合计，说明遍历完成
				  break;
				}
                String stationName = row.getCell(1).getStringCellValue();
				String equipmentCode = "";
                if(row.getCell(2)!=null){
                    //设置单元格类型
                    row.getCell(2).setCellType(CellType.STRING);
                    //获取单元格数据
                    equipmentCode = row.getCell(2).getStringCellValue();
                }
                String price = "0";//row.getCell(3).getRichStringCellValue().getString(); 不再导入单价
                String sales = row.getCell(3).getRichStringCellValue().getString();
                String sales_amount = row.getCell(4).getRichStringCellValue().getString();
                one.put("lineName",lineName);
                one.put("stationName",stationName);
                one.put("equipmentCode",equipmentCode);
                //one.put("unitPrice",price);
                one.put("compareTotalNumberSales",sales);
                one.put("compareSubtotalTotal",sales_amount);
                list.add(one);
            }
            JSONObject res=new JSONObject();
            res.put("list",list);
            res.put("static_date",static_date);
            redisTemplateConfig.redisTemplate().opsForValue().set(UserInfo.getUserCode()+"_export",res.toJSONString());
            result.setData(list);
        }catch (Exception e){
            e.printStackTrace();
            log.error("文件上传处理失败{}",e);
            result.setMsg("文件上传处理失败");
            result.setResult(ResultDetailMsg.MONDIFY_FILE_FAILD);
        }
        return result;
    }

    /**
     * 导入数据,线网导入确认操作
     * @param
     * @return
     * @throws Exception
     */
    @RequestMapping("/importDataSwAuInputInternetTicketEquipmentDetails")
    @ApiOperation(value = "导入数据,线网导入确认操作", notes = "导入数据,线网导入确认操作")
    public Result importDataSwAuInputInternetTicketEquipmentDetails(HttpServletRequest req) throws Exception {
        Result result=new Result(ResultDetailMsg.OK);
        try {			
            String _export = (String) redisTemplateConfig.redisTemplate().opsForValue().get(UserInfo.getUserCode()+"_export");			
            JSONObject upload_res = JSONObject.parseObject(_export);
            JSONArray parse = upload_res.getJSONArray("list");			
            String static_date_str = upload_res.getString("static_date");			

            Date static_date=DateFormat.panDuanDate(static_date_str);			
			
            for(int i=0;i<parse.size();i++){				
                JSONObject jsonObject = parse.getJSONObject(i);
                String lineName=jsonObject.getString("lineName");
                String stationName=jsonObject.getString("stationName");
                QueryWrapper<SwParamLine> queryWrapperLine=new QueryWrapper<>();
                queryWrapperLine.eq("name",lineName);
                List<SwParamLine> swParamLines = swParamLineMapper.selectList(queryWrapperLine);
                String lineCode="";
                String stationCode="";
                if(swParamLines!=null && swParamLines.size()>0){
                    lineCode=swParamLines.get(0).getCode();

                    QueryWrapper<SwParamStation> queryWrapperStation=new QueryWrapper<>();
                    queryWrapperStation.eq("name",stationName);
                    queryWrapperStation.eq("line_code",lineCode);
                    List<SwParamStation> swParamStations = swParamStationMapper.selectList(queryWrapperStation);
                    if(swParamStations!=null && swParamStations.size()>0){
                        stationCode=swParamStations.get(0).getCode();
                    }
                }				

                /**
                 *   导入存在就update发售数量，发售金额、差异金额、差异数量   （修改条件：线路站点、设备编号、收益日期）
                 *   线网导入后对比数量和对比金额 同时存确认数量和确认金额
                 *   不存在insert
                 */
                SwAuInternetTicketEquipment swAuInternetTicketEquipment = JSONObject.parseObject(jsonObject.toJSONString(), SwAuInternetTicketEquipment.class);
                SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");
                SwAuInternetTicketEquipment ticketEquipment = swAuInternetTicketEquipmentMapper.selectOne(Wrappers.<SwAuInternetTicketEquipment>lambdaQuery()
                        .eq(SwAuInternetTicketEquipment::getLineCode, lineCode)
                        .eq(SwAuInternetTicketEquipment::getStationCode, stationCode)
					    .eq(SwAuInternetTicketEquipment::getDelFlag,"0")
                        .eq(SwAuInternetTicketEquipment::getEquipmentCode, swAuInternetTicketEquipment.getEquipmentCode())
                        .eq(SwAuInternetTicketEquipment::getRevenueDate, dff.format(static_date)));
				
                if(ticketEquipment!=null){
                    SwAuInternetTicketEquipment equipment = new SwAuInternetTicketEquipment();
                    equipment.setId(ticketEquipment.getId());
                    equipment.setCompareTotalNumberSales(swAuInternetTicketEquipment.getCompareTotalNumberSales());
                    equipment.setCompareSubtotalTotal(swAuInternetTicketEquipment.getCompareSubtotalTotal()==null?new BigDecimal(0):swAuInternetTicketEquipment.getCompareSubtotalTotal().multiply(new BigDecimal(100)));
                    equipment.setConfirmNumber(equipment.getCompareTotalNumberSales());
                    equipment.setConfirmAmount(equipment.getCompareSubtotalTotal());
					
                    swAuInternetTicketEquipmentMapper.updateById(equipment);					
                }else {
                    swAuInternetTicketEquipment.setCompareSubtotalTotal(swAuInternetTicketEquipment.getCompareSubtotalTotal()==null?new BigDecimal(0):swAuInternetTicketEquipment.getCompareSubtotalTotal().multiply(new BigDecimal(100)));
                    swAuInternetTicketEquipment.setConfirmNumber(swAuInternetTicketEquipment.getCompareTotalNumberSales());
                    swAuInternetTicketEquipment.setConfirmAmount(swAuInternetTicketEquipment.getCompareSubtotalTotal());
                    swAuInternetTicketEquipment.setLineCode(lineCode);
                    swAuInternetTicketEquipment.setStationCode(stationCode);
                    swAuInternetTicketEquipment.setRevenueDate(dff.format(static_date));
                    swAuInternetTicketEquipment.setDelFlag("0");
                    swAuInternetTicketEquipment.setCreatorName(UserInfo.getUserName());
                    swAuInternetTicketEquipment.setCreatorId(UserInfo.getId());
                    swAuInternetTicketEquipment.setCreateTime(new Date());					

                    //swAuInternetTicketEquipment = (SwAuInternetTicketEquipment)ObejctFieldsValueNotNull.setValueNoNull(SwAuInternetTicketEquipment.class, swAuInternetTicketEquipment);
                    swAuInternetTicketEquipmentMapper.insert(swAuInternetTicketEquipment);				
                }




//                SwAuInternetTicketEquipmentDetails swAuInternetTicketEquipmentDetails = JSONObject.parseObject(jsonObject.toJSONString(), SwAuInternetTicketEquipmentDetails.class);
//                swAuInternetTicketEquipmentDetails.setUnitPrice(swAuInternetTicketEquipmentDetails.getUnitPrice()==null?new BigDecimal(0):swAuInternetTicketEquipmentDetails.getUnitPrice().multiply(new BigDecimal(100)));
//                swAuInternetTicketEquipmentDetails.setTotalSaleAmount(swAuInternetTicketEquipmentDetails.getTotalSaleAmount()==null?new BigDecimal(0):swAuInternetTicketEquipmentDetails.getTotalSaleAmount().multiply(new BigDecimal(100)));
//                swAuInternetTicketEquipmentDetails.setLineCode(lineCode);
//                swAuInternetTicketEquipmentDetails.setStationCode(stationCode);
//                SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");
//                swAuInternetTicketEquipmentDetails.setStaticDate(dff.format(static_date));
//                swAuInternetTicketEquipmentDetails.setDelFlag("0");
//                swAuInternetTicketEquipmentDetails.setCreatorName(UserInfo.getUserName());
//                swAuInternetTicketEquipmentDetails.setCreatorId(UserInfo.getId());
//                Map<String,Object> p=new HashMap<>();
//                p.put("operation_code", swAuInternetTicketEquipmentDetails.getEquipmentCode());
//                List<SwPmDeviceOperationCode> swPmDeviceOperationCodes = swPmDeviceOperationCodeMapper.selectByMap(p);
//                if(swPmDeviceOperationCodes!=null && swPmDeviceOperationCodes.size()>0){
//                    swAuInternetTicketEquipmentDetails.setEquipmentId(swPmDeviceOperationCodes.get(0).getId());
//                    swAuInternetTicketEquipmentDetails.setEquipmentName(swAuInternetTicketEquipmentDetails.getEquipmentCode());
//                }
//                swAuInternetTicketEquipmentDetails = (SwAuInternetTicketEquipmentDetails)ObejctFieldsValueNotNull.setValueNoNull(SwAuInternetTicketEquipmentDetails.class, swAuInternetTicketEquipmentDetails);
//                swAuInternetTicketEquipmentMapper.insertSwAuInputInternetTicketEquipmentDetail(swAuInternetTicketEquipmentDetails);
            }
        }catch (Exception e){
            log.error("{}",e);
            result.setResult(ResultDetailMsg.CHECK_INSERT_ERROR);
            result.setMsg("导入库失败！");
        }
        return result;
    }



    /**
     * 协查导出
     * @param
     * @return
     */
    @ApiOperation(value = "导出", notes = "导出")
    @GetMapping({"/exportInternet"})
    @SysLog("协查导出")
    public void export(String ids, SwAuInternetTicketEquipment params,HttpServletResponse response) {
        tvmXianJinCengJiCYDXCService.export(ids,params,response);
    }

    /**
     * 协查导出数据 TODO 弃用
     * @param
     * @return
     * @throws Exception
     */
    @RequestMapping("/xcExportDataSwAuInputInternetTicketEquipments")
    @ApiOperation(value = "协查导出数据", notes = "协查导出数据")
    public void xcExportDataSwAuInputInternetTicketEquipments(HttpServletRequest req,HttpServletResponse response,SwAuInternetTicketEquipment swAuInternetTicketEquipment) throws Exception {
        Result result=new Result(ResultDetailMsg.OK);
        try {
            List<Map<String,Object>> res=tvmXianJinCengJiCYDXCService.getSwAuInputInternetTicketEquipmentList(swAuInternetTicketEquipment);
            Workbook workbook = ExcelUtil.createWorkbook(ExcelVersion.XLSX);
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);

            Sheet sheet = workbook.createSheet();
            sheet.setColumnWidth(0,20 * 256);
            sheet.setColumnWidth(1,20 * 256);
            sheet.setColumnWidth(2,20 * 256);
            sheet.setColumnWidth(3,20 * 256);
            sheet.setColumnWidth(4,20 * 256);
            sheet.setColumnWidth(5,20 * 256);
            sheet.setColumnWidth(6,20 * 256);
            sheet.setColumnWidth(7,20 * 256);
            sheet.setColumnWidth(8,20 * 256);
            sheet.setColumnWidth(9,20 * 256);
            sheet.setColumnWidth(10,20 * 256);
            sheet.setColumnWidth(11,20 * 256);
            sheet.setColumnWidth(12,20 * 256);
            sheet.setColumnWidth(13,20 * 256);
            setTittle(sheet,cellStyle);
            for(int i=0;i<res.size();i++){
                Map<String,Object> map=res.get(i);
                String revenueDate= DateFormat.date_ex_str((Date) map.get("revenueDate"));
                //String endTime= DateFormat.date_ex_str((Date) map.get("endTime"));

                String lineCode= (String) map.get("lineCode");
                String stationCode= (String) map.get("stationCode");

                String line_name= (String) map.get("line_name");
                String station_name= (String) map.get("station_name");

//                String sql="select equipment_code from sw_au_internet_ticket_equipment_details where line_code='"+lineCode+"' and station_code='"+stationCode+"'"+
//                        " and static_date >='"+startTime+"' and static_date<='"+endTime+"' ";
//                List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
//                String equipment_code=String.valueOf(maps.size()>0?maps.get(0).get("equipment_code"):"");
//                Integer compareTotalNumberSales=(Integer) map.get("compareTotalNumberSales");//发售数量
//                BigDecimal compareSubtotalTotal=(BigDecimal)map.get("compareSubtotalTotal");//发售金额
//
//                String sql_cz="select equipment_code from sw_st_internet_ticket_equipment where line_code='"+lineCode+"' and station_code='"+stationCode+"'"+
//                        " and start_time = '"+startTime+"' and end_time = '"+endTime+"' ";
//                List<Map<String, Object>> maps_cz = JdbcUtil.exeSqlQuery(sql_cz);
//
//                Integer compareTotalNumberSales_cz=0;//车站发售数量
//                BigDecimal compareSubtotalTotal_cz=new BigDecimal(0);//车站发售金额
//                if(maps_cz.size()>0){
//                    compareTotalNumberSales_cz=(Integer) (map.get("totalNumberSales"));//车站发售数量
//                    compareSubtotalTotal_cz= (BigDecimal)map.get("subtotalTotal");//车站发售金额
//                }

                String equipment_code=String.valueOf(map.size()>0?map.get("equipment_code"):"");

                //发售数量
                Integer compareTotalNumberSales=0;
                //发售金额
                BigDecimal compareSubtotalTotal=new BigDecimal(0);
                if(map.get("compareTotalNumberSales")!=null){
                    //发售数量
                    compareTotalNumberSales=(Integer) map.get("compareTotalNumberSales");
                }
                if(map.get("compareSubtotalTotal")!=null){
                    //发售金额
                    compareSubtotalTotal=(BigDecimal)map.get("compareSubtotalTotal");
                }

                //车站发售数量
                Integer compareTotalNumberSales_cz=0;
                //车站发售金额
                BigDecimal compareSubtotalTotal_cz=new BigDecimal(0);
                if(map.get("totalNumberSales")!=null){
                    //车站发售数量
                    compareTotalNumberSales_cz=(Integer) (map.get("totalNumberSales"));
                }
                if(map.get("subtotalTotal")!=null){
                    //车站发售金额
                    compareSubtotalTotal_cz=(BigDecimal)map.get("subtotalTotal");
                }
                Integer diff_sales=compareTotalNumberSales-compareTotalNumberSales_cz;
                BigDecimal diff_amount=compareSubtotalTotal.subtract(compareSubtotalTotal_cz);

//                BigDecimal confirmAmount=(BigDecimal) map.get("confirmAmount");
//                Integer confirmNumber=(Integer) map.get("confirmNumber");

                String diff_reason=String.valueOf(map.get("diffReason"));



                Row row = sheet.createRow(i + 3);
                Cell cell_startTime = row.createCell(0);
                cell_startTime.setCellValue(revenueDate);

//                Cell cell_endTime = row.createCell(1);
//                cell_endTime.setCellValue(startTime);

                Cell cell_lineCode = row.createCell(1);
                cell_lineCode.setCellValue(line_name);

                Cell cell_stationCode = row.createCell(2);
                cell_stationCode.setCellValue(station_name);

                Cell cell_equipment_code = row.createCell(3);
                cell_equipment_code.setCellValue(equipment_code);

                Cell cell_queation = row.createCell(4);
                cell_queation.setCellValue("结账单数据与系统不一致");

                Cell cell_sals_num = row.createCell(5);
                cell_sals_num.setCellValue(compareTotalNumberSales);
                Cell cell_sals_amount = row.createCell(6);
                cell_sals_amount.setCellValue(compareSubtotalTotal.doubleValue());

                Cell cell_sals_num_cz = row.createCell(7);
                cell_sals_num_cz.setCellValue(compareTotalNumberSales_cz);
                Cell cell_sals_amount_cz = row.createCell(8);
                cell_sals_amount_cz.setCellValue(compareSubtotalTotal_cz.doubleValue());

                Cell cell_diff_num = row.createCell(9);
                cell_diff_num.setCellValue(diff_sales);
                Cell cell_diff_amount = row.createCell(10);
                cell_diff_amount.setCellValue(diff_amount.doubleValue());

                Cell cell_confirm_num = row.createCell(11);
                cell_confirm_num.setCellValue("");
                Cell cell_confirm_amount = row.createCell(12);
                cell_confirm_amount.setCellValue("");

                Cell cell_diff_reason = row.createCell(13);
                cell_diff_reason.setCellValue(diff_reason);
            }
            //起始行，结束行，起始列，结束列
            //第一行的
            CellRangeAddress region_first = new CellRangeAddress(0, 0, 0, 13);
            //第二行的 中软后台数据列
            CellRangeAddress region_second_zr = new CellRangeAddress(1, 1, 5, 6);
            //第二行的 设备水单数据
            CellRangeAddress region_second_cz = new CellRangeAddress(1, 1, 7, 8);
            //第二行的 差异
            CellRangeAddress region_second_cy = new CellRangeAddress(1, 1, 9, 10);
            //第二行的 确认结算
            CellRangeAddress region_second_js = new CellRangeAddress(1, 1, 11, 12);
            sheet.addMergedRegion(region_first);
            sheet.addMergedRegion(region_second_zr);
            sheet.addMergedRegion(region_second_cz);
            sheet.addMergedRegion(region_second_cy);
            sheet.addMergedRegion(region_second_js);

            ByteArrayOutputStream arrayOutputStream = new ByteArrayOutputStream();;
            OutputStream output = new BufferedOutputStream(arrayOutputStream);
            workbook.write(output);
            //创建ByteArrayResource用ByteArray输出流的字节数组
            InputStreamSource inputStreamSource = new ByteArrayResource(arrayOutputStream.toByteArray());
            output.close();
            DownLoadUtil.downloadUtil(response,inputStreamSource.getInputStream(),"互联网售票机协查导出.xlsx");
        }catch (Exception e){
            log.error("{}",e);
            result.setResult(ResultDetailMsg.CHECK_INSERT_ERROR);
            result.setMsg("协查导出失败！");
        }
//        return result;
    }

    private void setTittle(Sheet sheet,CellStyle cellStyle){
        Row row1 = sheet.createRow(0);
        Cell cell_startTime = row1.createCell(0);
        cell_startTime.setCellStyle(cellStyle);
        cell_startTime.setCellValue("网络售票机发售统计");

        Cell cell_endTime = row1.createCell(1);
        cell_endTime.setCellStyle(cellStyle);
        cell_endTime.setCellValue("");

        Cell cell_lineCode = row1.createCell(2);
        cell_lineCode.setCellStyle(cellStyle);
        cell_lineCode.setCellValue("");

        Cell cell_stationCode = row1.createCell(3);
        cell_stationCode.setCellStyle(cellStyle);
        cell_stationCode.setCellValue("");

        Cell cell_equipment_code = row1.createCell(4);
        cell_equipment_code.setCellStyle(cellStyle);
        cell_equipment_code.setCellValue("");

        Cell cell_queation = row1.createCell(5);
        cell_queation.setCellStyle(cellStyle);
        cell_queation.setCellValue("");

        Cell cell_sals_num = row1.createCell(6);
        cell_sals_num.setCellStyle(cellStyle);
        cell_sals_num.setCellValue("");
        Cell cell_sals_amount = row1.createCell(7);
        cell_sals_amount.setCellStyle(cellStyle);
        cell_sals_amount.setCellValue("");

        Cell cell_sals_num_cz = row1.createCell(8);
        cell_sals_num_cz.setCellStyle(cellStyle);
        cell_sals_num_cz.setCellValue("");
        Cell cell_sals_amount_cz = row1.createCell(9);
        cell_sals_amount_cz.setCellStyle(cellStyle);
        cell_sals_amount_cz.setCellValue("");

        Cell cell_diff_num = row1.createCell(10);
        cell_diff_num.setCellStyle(cellStyle);
        cell_diff_num.setCellValue("");
        Cell cell_diff_amount = row1.createCell(11);
        cell_diff_amount.setCellStyle(cellStyle);
        cell_diff_amount.setCellValue("");

        Cell cell_confirm_num = row1.createCell(12);
        cell_confirm_num.setCellStyle(cellStyle);
        cell_confirm_num.setCellValue("");
        Cell cell_confirm_amount = row1.createCell(13);
        cell_confirm_amount.setCellStyle(cellStyle);
        cell_confirm_amount.setCellValue("");

        Cell cell_diff_reason = row1.createCell(14);
        cell_diff_reason.setCellStyle(cellStyle);
        cell_diff_reason.setCellValue("");


        Row row2 = sheet.createRow(1);
        Cell cell_startTime2 = row2.createCell(0);
        cell_startTime2.setCellStyle(cellStyle);
        cell_startTime2.setCellValue("");

        Cell cell_endTime2 = row2.createCell(1);
        cell_endTime2.setCellStyle(cellStyle);
        cell_endTime2.setCellValue("");

        Cell cell_lineCode2 = row2.createCell(2);
        cell_lineCode2.setCellStyle(cellStyle);
        cell_lineCode2.setCellValue("");

        Cell cell_stationCode2 = row2.createCell(3);
        cell_stationCode2.setCellStyle(cellStyle);
        cell_stationCode2.setCellValue("");

        Cell cell_equipment_code2 = row2.createCell(4);
        cell_equipment_code2.setCellStyle(cellStyle);
        cell_equipment_code2.setCellValue("");

        Cell cell_queation2 = row2.createCell(5);
        cell_queation2.setCellStyle(cellStyle);
        cell_queation2.setCellValue("");

        Cell cell_sals_num2 = row2.createCell(6);
        cell_sals_num2.setCellStyle(cellStyle);
        cell_sals_num2.setCellValue("中软后台数据");
        Cell cell_sals_amount2 = row2.createCell(7);
        cell_sals_amount2.setCellStyle(cellStyle);
        cell_sals_amount2.setCellValue("");

        Cell cell_sals_num_cz2 = row2.createCell(8);
        cell_sals_num_cz2.setCellStyle(cellStyle);
        cell_sals_num_cz2.setCellValue("设备水单数据");
        Cell cell_sals_amount_cz2 = row2.createCell(9);
        cell_sals_amount_cz2.setCellStyle(cellStyle);
        cell_sals_amount_cz2.setCellValue("");

        Cell cell_diff_num2 = row2.createCell(10);
        cell_diff_num2.setCellStyle(cellStyle);
        cell_diff_num2.setCellValue("差异");
        Cell cell_diff_amount2 = row2.createCell(11);
        cell_diff_amount2.setCellStyle(cellStyle);
        cell_diff_amount2.setCellValue("");

        Cell cell_confirm_num2 = row2.createCell(12);
        cell_confirm_num2.setCellStyle(cellStyle);
        cell_confirm_num2.setCellValue("确认结算");
        Cell cell_confirm_amount2 = row2.createCell(13);
        cell_confirm_amount2.setCellStyle(cellStyle);
        cell_confirm_amount2.setCellValue("");

        Cell cell_diff_reason2 = row2.createCell(14);
        cell_diff_reason2.setCellStyle(cellStyle);
        cell_diff_reason2.setCellValue("差异原因");


        Row row3 = sheet.createRow(2);
        Cell cell_startTime3 = row3.createCell(0);
        cell_startTime3.setCellStyle(cellStyle);
        cell_startTime3.setCellValue("开始日期");

        Cell cell_endTime3 = row3.createCell(1);
        cell_endTime3.setCellStyle(cellStyle);
        cell_endTime3.setCellValue("结束日期");

        Cell cell_lineCode3 = row3.createCell(2);
        cell_lineCode3.setCellStyle(cellStyle);
        cell_lineCode3.setCellValue("线路");

        Cell cell_stationCode3 = row3.createCell(3);
        cell_stationCode3.setCellStyle(cellStyle);
        cell_stationCode3.setCellValue("站点");

        Cell cell_equipment_code3 = row3.createCell(4);
        cell_equipment_code3.setCellStyle(cellStyle);
        cell_equipment_code3.setCellValue("设备号");

        Cell cell_queation3 = row3.createCell(5);
        cell_queation3.setCellStyle(cellStyle);
        cell_queation3.setCellValue("问题种类");

        Cell cell_sals_num3 = row3.createCell(6);
        cell_sals_num3.setCellStyle(cellStyle);
        cell_sals_num3.setCellValue("发售数量");
        Cell cell_sals_amount3 = row3.createCell(7);
        cell_sals_amount3.setCellStyle(cellStyle);
        cell_sals_amount3.setCellValue("发售金额/元");

        Cell cell_sals_num_cz3 = row3.createCell(8);
        cell_sals_num_cz3.setCellStyle(cellStyle);
        cell_sals_num_cz3.setCellValue("发售数量");
        Cell cell_sals_amount_cz3 = row3.createCell(9);
        cell_sals_amount_cz3.setCellStyle(cellStyle);
        cell_sals_amount_cz3.setCellValue("发售金额/元");

        Cell cell_diff_num3 = row3.createCell(10);
        cell_diff_num3.setCellStyle(cellStyle);
        cell_diff_num3.setCellValue("发售数量");
        Cell cell_diff_amount3 = row3.createCell(11);
        cell_diff_amount3.setCellStyle(cellStyle);
        cell_diff_amount3.setCellValue("发售金额/元");

        Cell cell_confirm_num3 = row3.createCell(12);
        cell_confirm_num3.setCellStyle(cellStyle);
        cell_confirm_num3.setCellValue("发售数量");
        Cell cell_confirm_amount3 = row3.createCell(13);
        cell_confirm_amount3.setCellStyle(cellStyle);
        cell_confirm_amount3.setCellValue("发售金额/元");

        Cell cell_diff_reason3 = row3.createCell(14);
        cell_diff_reason3.setCellStyle(cellStyle);
        cell_diff_reason3.setCellValue("");
    }

    /**
     * 上传协查结果数据
     * @param file
     * @return
     * @throws Exception
     */
    @RequestMapping("/uploadXCResult")
    @ApiOperation(value = "上传协查结果数据", notes = "上传协查结果数据")
    public Result uploadXCResult(MultipartFile file,HttpServletRequest req) throws Exception {
        Result result = new Result(ResultDetailMsg.OK);
        String fileName = file.getOriginalFilename();
        InputStream inputStream = file.getInputStream();
        Workbook wb=null;
        //根据文件后缀（xls/xlsx）进行判断
        if (fileName.endsWith("xls")){
            wb = new HSSFWorkbook(inputStream);
        }else if (fileName.endsWith("xlsx")){
            wb = new XSSFWorkbook(inputStream);
        }else {
            result.setMsg("请使用xls、xlsx格式文件");
            result.setResult(ResultDetailMsg.CHECK_UPDATE_ERROR);
            return result;
        }
        List<Map<String,Object>> list=new ArrayList<>();
        Sheet sheetAt = wb.getSheetAt(0);
        int lastRowNum = sheetAt.getLastRowNum();
        for(int i=3;i<=lastRowNum;i++){
            Row row = sheetAt.getRow(i);
//            String startTime = ExcelCellUtil.getCellContent(row.getCell(0));
////            String endTime = ExcelCellUtil.getCellContent(row.getCell(1));
            String revenueDate = ExcelCellUtil.getCellContent(row.getCell(0));
            String lineName = ExcelCellUtil.getCellContent(row.getCell(1));
            String stationName = ExcelCellUtil.getCellContent(row.getCell(2));
            String equipment_code = ExcelCellUtil.getCellContent(row.getCell(3));
            String question_type = ExcelCellUtil.getCellContent(row.getCell(4));
            String sale_nums_zr = ExcelCellUtil.getCellContent(row.getCell(5));
            String sale_amount_zr = ExcelCellUtil.getCellContent(row.getCell(6));
            String sale_nums_cz = ExcelCellUtil.getCellContent(row.getCell(7));
            String sale_amount_cz = ExcelCellUtil.getCellContent(row.getCell(8));
            String sale_nums_cy = ExcelCellUtil.getCellContent(row.getCell(9));
            String sale_amount_cy = ExcelCellUtil.getCellContent(row.getCell(10));
            String sale_nums_confirm = ExcelCellUtil.getCellContent(row.getCell(11));
            String sale_amount_confirm = ExcelCellUtil.getCellContent(row.getCell(12));
            String reason = ExcelCellUtil.getCellContent(row.getCell(13));

            Map<String,Object> map=new HashMap<>();
//            map.put("startTime",startTime);
//            map.put("endTime",endTime);
            map.put("revenueDate",revenueDate);
            map.put("lineName",lineName);
            map.put("stationName",stationName);
            map.put("equipment_code",equipment_code);
            map.put("question_type",question_type);
            map.put("sale_nums_zr",sale_nums_zr);
            map.put("sale_amount_zr",sale_amount_zr);
            map.put("sale_nums_cz",sale_nums_cz);
            map.put("sale_amount_cz",sale_amount_cz);
            map.put("sale_nums_cy",sale_nums_cy);
            map.put("sale_amount_cy",sale_amount_cy);
            map.put("sale_nums_confirm",sale_nums_confirm);
            map.put("sale_amount_confirm",sale_amount_confirm);
            map.put("reason",reason);
            list.add(map);
        }
        result.setData(list);
        redisTemplateConfig.redisTemplate().opsForValue().set("xc_import_"+UserInfo.getUserCode(),JSONObject.toJSONString(list));
        return result;
    }

    /**
     * 导入数据
     * @param
     * @return
     * @throws Exception
     */
    @RequestMapping("/import_xc_result")
    @ApiOperation(value = "导入数据", notes = "导入数据")
    public Result import_xc_result() throws Exception {
        Result res=new Result(ResultDetailMsg.OK);
        Object o = redisTemplateConfig.redisTemplate().opsForValue().get("xc_import_" + UserInfo.getUserCode());
        JSONArray js= JSONArray.parseArray(o.toString());
        for(int i=0;i<js.size();i++){
            JSONObject jsonObject = js.getJSONObject(i);

//            String startTime = jsonObject.getString("startTime");
//            String endTime = jsonObject.getString("endTime");
            String revenueDate = jsonObject.getString("revenueDate");
            String lineName = jsonObject.getString("lineName");
            String stationName = jsonObject.getString("stationName");

            QueryWrapper<SwParamLine> queryWrapperLine=new QueryWrapper<>();
            queryWrapperLine.eq("name",lineName);
            List<SwParamLine> swParamLines = swParamLineMapper.selectList(queryWrapperLine);
            String lineCode="";
            String stationCode="";
            if(swParamLines!=null && swParamLines.size()>0){
                lineCode=swParamLines.get(0).getCode();

                QueryWrapper<SwParamStation> queryWrapperStation=new QueryWrapper<>();
                queryWrapperStation.eq("name",stationName);
                queryWrapperStation.eq("line_code",lineCode);
                List<SwParamStation> swParamStations = swParamStationMapper.selectList(queryWrapperStation);
                if(swParamStations!=null && swParamStations.size()>0){
                    stationCode=swParamStations.get(0).getCode();
                }
            }


            String equipment_code = jsonObject.getString("equipment_code");
            String question_type = jsonObject.getString("question_type");
            String sale_nums_zr = jsonObject.getString("sale_nums_zr");
            String sale_amount_zr = jsonObject.getString("sale_amount_zr");
            String sale_nums_cz = jsonObject.getString("sale_nums_cz");
            String sale_amount_cz = jsonObject.getString("sale_amount_cz");
            String sale_nums_cy = jsonObject.getString("sale_nums_cy");
            String sale_amount_cy = jsonObject.getString("sale_amount_cy");
            String sale_nums_confirm = jsonObject.getString("sale_nums_confirm");
            String sale_amount_confirm = jsonObject.getString("sale_amount_confirm");
            String reason = jsonObject.getString("reason");

            SwAuInternetTicketEquipment swAuInternetTicketEquipment=new SwAuInternetTicketEquipment();
            swAuInternetTicketEquipment.setLineCode(lineCode);
            swAuInternetTicketEquipment.setStationCode(stationCode);
//            swAuInternetTicketEquipment.setStartTime(startTime);
//            swAuInternetTicketEquipment.setEndTime(endTime);
            swAuInternetTicketEquipment.setDelFlag("0");//增加删除标识的过滤
            swAuInternetTicketEquipment.setRevenueDate(revenueDate);
            swAuInternetTicketEquipment.setEquipmentCode(equipment_code);
            List<Map<String, Object>> swAuInternetTicketEquipments = swAuInternetTicketEquipmentMapper.getSwAuInputInternetTicketEquipmentList(swAuInternetTicketEquipment);
            if(swAuInternetTicketEquipments.size()>0){
                Map<String, Object> map = swAuInternetTicketEquipments.get(0);
                swAuInternetTicketEquipment.setId((Integer) map.get("id"));
            }
            swAuInternetTicketEquipment.setCompareTotalNumberSales(Integer.parseInt(sale_nums_zr));
            swAuInternetTicketEquipment.setCompareSubtotalTotal(new BigDecimal(sale_amount_zr).multiply(new BigDecimal(100)));
            swAuInternetTicketEquipment.setConfirmAmount(new BigDecimal(sale_amount_confirm).multiply(new BigDecimal(100)));
            swAuInternetTicketEquipment.setConfirmNumber(Integer.parseInt(sale_nums_confirm));
            swAuInternetTicketEquipment.setDiffReason(reason);

            UpdateWrapper<SwAuInternetTicketEquipment> updateWrapper=new UpdateWrapper<>();
            updateWrapper.eq("id",swAuInternetTicketEquipment.getId());
            if(swAuInternetTicketEquipments.size()>0){
                swAuInternetTicketEquipmentMapper.update(swAuInternetTicketEquipment,updateWrapper);
            }
        }
        return res;
    }


    /**
     * 调用互联网售票机修改方法
     * @param swAuInternetTicketEquipment
     * @return
     */
    @PostMapping("/inner")
    @Inner
    public R feignSave(@Valid @RequestBody SwAuInternetTicketEquipment swAuInternetTicketEquipment) {
        return R.ok(swAuInternetTicketEquipmentMapper.updateById(swAuInternetTicketEquipment));
    }
}
